#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
湖州地区数据统计脚本
分析德清、长兴、安吉三个地方的街道社区和小区数据统计
"""

import sqlite3
import json
from collections import defaultdict

def get_database_statistics():
    """获取数据库统计信息"""
    conn = sqlite3.connect('huzhou_real_data.db')
    cursor = conn.cursor()
    
    # 基础统计
    cursor.execute("SELECT COUNT(*) FROM streets")
    total_streets = cursor.fetchone()[0]
    
    cursor.execute("SELECT COUNT(*) FROM communities")
    total_communities = cursor.fetchone()[0]
    
    cursor.execute("SELECT COUNT(*) FROM residential_areas")
    total_areas = cursor.fetchone()[0]
    
    # 按地区统计
    cursor.execute("""
        SELECT district_name, COUNT(*) as count
        FROM streets
        GROUP BY district_name
        ORDER BY count DESC
    """)
    district_streets = cursor.fetchall()
    
    # 按街道统计社区数量
    cursor.execute("""
        SELECT street_name, COUNT(*) as count
        FROM communities
        GROUP BY street_name
        ORDER BY count DESC
    """)
    street_communities = cursor.fetchall()
    
    # 按社区统计小区数量
    cursor.execute("""
        SELECT community_name, COUNT(*) as count
        FROM residential_areas
        GROUP BY community_name
        ORDER BY count DESC
    """)
    community_areas = cursor.fetchall()
    
    conn.close()
    
    return {
        'total_streets': total_streets,
        'total_communities': total_communities,
        'total_areas': total_areas,
        'district_streets': district_streets,
        'street_communities': street_communities,
        'community_areas': community_areas
    }

def print_statistics():
    """打印统计信息"""
    stats = get_database_statistics()
    
    print("\n" + "="*60)
    print("🏘️ 湖州地区数据统计报告")
    print("="*60)
    
    # 总体统计
    print(f"\n📊 总体统计:")
    print(f"  街道总数: {stats['total_streets']}")
    print(f"  社区总数: {stats['total_communities']}")
    print(f"  小区总数: {stats['total_areas']}")
    
    # 按地区统计
    print(f"\n🏢 按地区统计街道数量:")
    for district, count in stats['district_streets']:
        print(f"  {district}: {count} 个街道")
    
    # 街道社区统计
    print(f"\n📂 街道社区分布 (前10名):")
    for i, (street, count) in enumerate(stats['street_communities'][:10], 1):
        print(f"  {i:2d}. {street}: {count} 个社区")
    
    # 社区小区统计
    print(f"\n🏠 社区小区分布 (前10名):")
    for i, (community, count) in enumerate(stats['community_areas'][:10], 1):
        print(f"  {i:2d}. {community}: {count} 个小区")
    
    # 计算平均值
    avg_communities_per_street = stats['total_communities'] / stats['total_streets']
    avg_areas_per_community = stats['total_areas'] / stats['total_communities']
    
    print(f"\n📈 平均值统计:")
    print(f"  平均每个街道社区数: {avg_communities_per_street:.1f}")
    print(f"  平均每个社区小区数: {avg_areas_per_community:.1f}")

def get_detailed_analysis():
    """获取详细分析"""
    conn = sqlite3.connect('huzhou_real_data.db')
    cursor = conn.cursor()
    
    # 获取完整的层级数据
    cursor.execute("""
        SELECT 
            s.district_name,
            s.name as street_name,
            c.name as community_name,
            COUNT(r.id) as area_count
        FROM streets s
        LEFT JOIN communities c ON s.name = c.street_name
        LEFT JOIN residential_areas r ON c.name = r.community_name
        GROUP BY s.district_name, s.name, c.name
        ORDER BY s.district_name, s.name, c.name
    """)
    
    results = cursor.fetchall()
    conn.close()
    
    # 组织数据结构
    analysis = defaultdict(lambda: defaultdict(lambda: defaultdict(list)))
    
    for district, street, community, area_count in results:
        if community:  # 只处理有社区的数据
            analysis[district][street][community] = area_count
    
    return analysis

def print_detailed_analysis():
    """打印详细分析"""
    analysis = get_detailed_analysis()
    
    print("\n" + "="*60)
    print("🔍 详细层级分析")
    print("="*60)
    
    for district in ['德清县', '长兴县', '安吉县']:
        print(f"\n📁 {district}")
        print("-" * 40)
        
        district_data = analysis[district]
        total_streets = len(district_data)
        total_communities = sum(len(street_data) for street_data in district_data.values())
        total_areas = sum(sum(street_data.values()) for street_data in district_data.values())
        
        print(f"  街道数: {total_streets}")
        print(f"  社区数: {total_communities}")
        print(f"  小区数: {total_areas}")
        
        # 显示前3个街道的详细信息
        for i, (street, street_data) in enumerate(district_data.items()):
            if i >= 3:  # 只显示前3个
                break
            print(f"\n  📂 {street} ({len(street_data)} 个社区)")
            for community, area_count in list(street_data.items())[:3]:  # 只显示前3个社区
                print(f"    📄 {community} ({area_count} 个小区)")

def export_analysis_to_json():
    """导出分析结果到JSON"""
    analysis = get_detailed_analysis()
    
    # 转换为可序列化的格式
    serializable_analysis = {}
    for district, district_data in analysis.items():
        serializable_analysis[district] = {}
        for street, street_data in district_data.items():
            serializable_analysis[district][street] = dict(street_data)
    
    # 添加统计信息
    stats = get_database_statistics()
    output = {
        'statistics': {
            'total_streets': stats['total_streets'],
            'total_communities': stats['total_communities'],
            'total_areas': stats['total_areas'],
            'district_breakdown': dict(stats['district_streets'])
        },
        'detailed_analysis': serializable_analysis
    }
    
    with open('huzhou_analysis_report.json', 'w', encoding='utf-8') as f:
        json.dump(output, f, ensure_ascii=False, indent=2)
    
    print(f"\n📄 分析报告已导出到: huzhou_analysis_report.json")

def main():
    """主函数"""
    print("🔍 湖州地区数据统计分析")
    print("="*60)
    
    # 基础统计
    print_statistics()
    
    # 详细分析
    print_detailed_analysis()
    
    # 导出报告
    export_analysis_to_json()
    
    print(f"\n✅ 统计分析完成！")

if __name__ == "__main__":
    main() 